﻿using System;
using Dapper;
using SilenthinkContract.Domain;

namespace SilenthinkService
{
    internal static partial class SqlTemplate
    {
        internal static class BaseTemplate
        {
            #region EmailAccount
            internal static SqlBuilder.Template CreateEmailAccount(EmailAccount o)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.BaseSchema.Table.EmailAccount)
                .Field("([Category],[Email],[DisplayName],[Host],[Port],[Username],[Password],[EnableSsl],[UseDefaultCredentials],[Status],[CreatedBy],[CreatedOn])VALUES(@Category,@Email,@DisplayName,@Host,@Port,@Username,@Password,@EnableSsl,@UseDefaultCredentials,@Status,@CreatedBy,@CreatedOn)")
                .AddParameters(new { o.Category, o.Email, o.DisplayName, o.Host, o.Port, o.Username, o.Password, o.EnableSsl, o.UseDefaultCredentials, o.Status, o.CreatedBy, o.CreatedOn });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            internal static SqlBuilder.Template UpdateEmailAccount(EmailAccount o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.BaseSchema.Table.EmailAccount)
                    .Field("[Category]=@Category", new { o.Category })
                    .Field("[Email]=@Email", new { o.Email })
                    .Field("[DisplayName]=@DisplayName", new { o.DisplayName })
                    .Field("[Host]=@Host", new { o.Host })
                    .Field("[Port]=@Port", new { o.Port })
                    .Field("[Username]=@Username", new { o.Username })
                    .Field("[Password]=@Password", new { o.Password })
                    .Field("[EnableSsl]=@EnableSsl", new { o.EnableSsl })
                    .Field("[UseDefaultCredentials]=@UseDefaultCredentials", new { o.UseDefaultCredentials })
                    .Field("[Status]=@Status", new { o.Status })
                    .Where("[Id] = @Id", new { o.Id }).Where("[Version]=@Version", new { o.Version });

                return builder.AddTemplate(BasicSingleTableSqlTemplate.Update);
            }

            internal static SqlBuilder.Template DeleteEmailAccount(int id)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.BaseSchema.Table.EmailAccount)
                .Where("[Id] = @Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Delete);
            }

            internal static SqlBuilder.Template GetEmailAccount(int id)
            {
                var builder = new SqlBuilder()
                .Field("*")
                .Table(DatabaseSchema.BaseSchema.Table.EmailAccount)
                .Where("[Id] = @Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.SelectTopOne);
            }

            internal static SqlBuilder.Template GetAllEmailAccount()
            {
                var builder = new SqlBuilder()
                .Field("*")
                .Table(DatabaseSchema.BaseSchema.Table.EmailAccount);

                return builder.AddTemplate(BasicSingleTableSqlTemplate.Select);
            }

            internal static SqlBuilder.Template GetPagedEmailAccount(int index = 1, int size = 20)
            {
                if (index < 1)
                {
                    index = 1;
                }
                if (size < 0 || size > 200)
                {
                    size = 20;
                }
                var builder = new SqlBuilder().Field("*")
                .Table(DatabaseSchema.BaseSchema.Table.EmailAccount)
                .OrderBy("id desc")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });
                /*
                .Where("");
                */
                return builder.AddTemplate(BasicSingleTableSqlTemplate.PagedSelect);
            }
            #endregion EmailAccount

            #region EmailTemplate
            internal static SqlBuilder.Template CreateEmailTemplate(EmailTemplate o)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.BaseSchema.Table.EmailTemplate)
                .Field("([Name],[BccEmailAddresses],[Subject],[Body],[Description],[CreatedBy],[CreatedOn])VALUES(@Name,@BccEmailAddresses,@Subject,@Body,@Description,@CreatedBy,@CreatedOn)")
                .AddParameters(new { o.Name, o.BccEmailAddresses, o.Subject, o.Body, o.Description,  o.CreatedBy, o.CreatedOn });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            internal static SqlBuilder.Template UpdateEmailTemplate(EmailTemplate o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.BaseSchema.Table.EmailTemplate)
                    .Field("[Name]=@Name", new { o.Name })
                    .Field("[BccEmailAddresses]=@BccEmailAddresses", new { o.BccEmailAddresses })
                    .Field("[Subject]=@Subject", new { o.Subject })
                    .Field("[Body]=@Body", new { o.Body })
                    .Field("[Description]=@Description", new { o.Description })
                    .Where("[Version]=@Version", new { o.Version })
                    .Where("[Id] = @Id", new { o.Id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Update);
            }

            internal static SqlBuilder.Template DeleteEmailTemplate(int id)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.BaseSchema.Table.EmailTemplate)
                .Where("[Id] = @Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Delete);
            }

            internal static SqlBuilder.Template GetEmailTemplate(int id)
            {
                var builder = new SqlBuilder()
                .Field("*")
                .Table(DatabaseSchema.BaseSchema.Table.EmailTemplate)
                .Where("[Id] = @Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.SelectTopOne);
            }

            internal static SqlBuilder.Template GetAllEmailTemplate()
            {
                var builder = new SqlBuilder()
                .Field("*")
                .Table(DatabaseSchema.BaseSchema.Table.EmailTemplate);
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Select);
            }

            internal static SqlBuilder.Template GetPagedEmailTemplate(int index = 1, int size = 20)
            {
                if (index < 1)
                {
                    index = 1;
                }
                if (size < 0 || size > 200)
                {
                    size = 20;
                }
                var builder = new SqlBuilder().Field("*")
                .Table(DatabaseSchema.BaseSchema.Table.EmailTemplate)
                .OrderBy("id desc")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });
                /*
                .Where("");
                */
                return builder.AddTemplate(BasicSingleTableSqlTemplate.PagedSelect);
            }

            #endregion EmailTemplate

            #region Bank
            internal static SqlBuilder.Template CreatePaymentBank(PaymentBank o)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.BaseSchema.Table.Bank)
                .Field("([Name],[Image],[Description],[Status],[CreatedBy],[CreatedOn])VALUES(@Name,@Image,@Description,@Status,@CreatedBy,@CreatedOn)")
                .AddParameters(new { o.Name, o.Image, o.Description, o.Status, o.CreatedBy, o.CreatedOn });
                return builder.AddTemplate(Dapper.SqlTemplate.Insert);
            }

            internal static SqlBuilder.Template UpdateBank(PaymentBank o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.BaseSchema.Table.Bank)
                    .Field("[Name]=@Name", new { o.Name })
                    .Field("[Image]=@Image", new { o.Image })
                    .Field("[Description]=@Description", new { o.Description })
                    .Field("[Status]=@Status", new { o.Status })
                    .Where("[Id] = @Id", new { o.Id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Update);
            }

            internal static SqlBuilder.Template DeleteBank(int id)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.BaseSchema.Table.Bank)
                .Where("[Id] = @Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Delete);
            }

            internal static SqlBuilder.Template GetBank(int id)
            {
                var builder = new SqlBuilder()
                .Field("*")
                .Table(DatabaseSchema.BaseSchema.Table.Bank)
                .Where("[Id] = @Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.SelectTopOne);
            }

            internal static SqlBuilder.Template GetAllBank()
            {
                var builder = new SqlBuilder()
                .Field("*")
                .Table(DatabaseSchema.BaseSchema.Table.Bank);

                return builder.AddTemplate(BasicSingleTableSqlTemplate.Select);
            }

            internal static SqlBuilder.Template GetAvailableBank(int platformId)
            {
                const string tpl = "SELECT [Id],[Name] FROM {0} WHERE  [Status]=1 AND Id NOT IN(SELECT BankId FROM {1} WHERE [PaymentPlatformId]=@PlatformId)";
                var sql = string.Format(tpl, DatabaseSchema.BaseSchema.Table.Bank, DatabaseSchema.BaseSchema.Table.EBank);
                return new SqlBuilder().AddParameters(new { platformId }).AddTemplate(sql);
            }

            internal static SqlBuilder.Template GetPagedBank(int index = 1, int size = 20, string name = null, bool? status = null)
            {
                CheckIndexSize(ref index, ref size);
                var builder = new SqlBuilder().Field("*")
                    .Table(DatabaseSchema.BaseSchema.Table.Bank)
                    .OrderBy("[Status] DESC")
                    .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });
                if (!String.IsNullOrEmpty(name))
                {
                    builder.Where("[Name] LIKE @Name", new { name = name + "%" });
                }
                if (status.HasValue)
                {
                    builder.Where("[Status] = @Status", new { status });
                }

                return builder.AddTemplate(BasicSingleTableSqlTemplate.PagedSelect);
            }

            #endregion Bank

            #region EBank
            internal static SqlBuilder.Template CreateEBank(PaymentEBank o)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.BaseSchema.Table.EBank)
                .Field("([BankId],[PlatformId],[GatewayCode],[Description],[DisplayOrder],[Status],[CreatedBy],[CreatedOn])VALUES(@BankId,@PlatformId,@GatewayCode,@Description,@DisplayOrder,@Status,@CreatedBy,@CreatedOn)")
                .AddParameters(new { o.BankId, o.PlatformId, o.GatewayCode, o.Description, o.DisplayOrder, o.Status, o.CreatedBy, o.CreatedOn });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            internal static SqlBuilder.Template UpdateEBank(PaymentEBank o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.BaseSchema.Table.EBank)
                    .Field("[BankId]=@BankId", new { o.BankId })
                    .Field("[PlatformId]=@PlatformId", new { o.PlatformId })
                    .Field("[GatewayCode]=@GatewayCode", new { o.GatewayCode })
                    .Field("[Description]=@Description", new { o.Description })
                    .Field("[DisplayOrder]=@DisplayOrder", new { o.DisplayOrder })
                    .Field("[Status]=@Status", new { o.Status })
                    .Where("[Id] = @Id", new { o.Id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Update);
            }

            internal static SqlBuilder.Template DeleteEBank(int id)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.BaseSchema.Table.EBank)
                .Where("[Id] = @Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Delete);
            }

            internal static SqlBuilder.Template GetEBank(int id, bool widthDetail = false)
            {
                if (widthDetail)
                {
                    const string tpl = "SELECT * FROM /**table**/ /**innerjoin**/ /**where**/";
                    var builder = new SqlBuilder()
                        .Table(DatabaseSchema.BaseSchema.Table.EBank + " a")
                        .InnerJoin(DatabaseSchema.BaseSchema.Table.Bank + " b on a.BankId=b.Id")
                        .InnerJoin(DatabaseSchema.BaseSchema.Table.PaymentPlatform + "  c on a.PaymentPlatformId=c.Id");
                    if (id > 0)
                    {
                        builder.Where("a.Id=@Id", new { id });
                    }
                    return builder.AddTemplate(tpl);
                }
                else
                {
                    var builder = new SqlBuilder()
                        .Field("*")
                        .Table(DatabaseSchema.BaseSchema.Table.EBank)
                        .Where("[Id] = @Id", new { id });
                    return builder.AddTemplate(BasicSingleTableSqlTemplate.SelectTopOne);
                }
            }

            internal static SqlBuilder.Template GetAllEBank(int? platformId = null, bool? status = null, string name = null)
            {
                //var sql ="select * from EBank a inner join Bank b on a.BankId=b.Id inner join dbo.PaymentPlatform c on a.PaymentPlatformId=c.Id";
                const string tpl = "SELECT * FROM /**table**/ /**innerjoin**/ /**where**/";
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.BaseSchema.Table.EBank + " a")
                    .InnerJoin(DatabaseSchema.BaseSchema.Table.Bank + " b on a.BankId=b.Id")
                    .InnerJoin(DatabaseSchema.BaseSchema.Table.PaymentPlatform + "  c on a.PaymentPlatformId=c.Id");
                if (platformId.HasValue && platformId.Value > 0)
                {
                    builder.Where("a.PaymentPlatformId=@PlatformId", new { platformId });
                }
                if (status.HasValue)
                {
                    builder.Where("a.Status = @Status", new { status });
                }
                if (!string.IsNullOrEmpty(name))
                {
                    builder.Where("b.Name LIKE @Name", new { name = name + "%" });
                }
                return builder.AddTemplate(tpl);
            }

            #endregion EBank

            #region PaymentPlatform
            internal static SqlBuilder.Template CreatePaymentPlatform(PaymentPlatform o)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.BaseSchema.Table.PaymentPlatform)
                .Field("([Name],[Image],[Description],[DisplayOrder],[Status],[CreatedBy],[CreatedOn])VALUES(@Name,@Image,@Description,@DisplayOrder,@Status,@CreatedBy,@CreatedOn)")
                .AddParameters(new { o.Name, o.Image, o.Description, o.DisplayOrder, o.Status, o.CreatedBy, o.CreatedOn });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            internal static SqlBuilder.Template UpdatePaymentPlatform(PaymentPlatform o)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.BaseSchema.Table.PaymentPlatform)
                .Field("[Name]=@Name", new { o.Name })
                .Field("[Image]=@Image", new { o.Image })
                .Field("[Description]=@Description", new { o.Description })
                .Field("[DisplayOrder]=@DisplayOrder", new { o.DisplayOrder })
                .Field("[Status]=@Status", new { o.Status })
                .Where("[Id] = @Id", new { o.Id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Update);
            }

            internal static SqlBuilder.Template DeletePaymentPlatform(int id)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.BaseSchema.Table.PaymentPlatform)
                .Where("[Id] = @Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Delete);
            }

            internal static SqlBuilder.Template GetPaymentPlatform(int id)
            {
                var builder = new SqlBuilder()
                .Field("*")
                .Table(DatabaseSchema.BaseSchema.Table.PaymentPlatform)
                .Where("[Id] = @Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.SelectTopOne);
            }

            internal static SqlBuilder.Template GetAllPaymentPlatform(bool? status = null, bool widthEbanks = false)
            {
                if (widthEbanks)
                {
                    var a = "SELECT * FROM {0} WHERE 1=1" + (status.HasValue ? " AND [Status]=@Status;" : string.Empty);
                    var b = "SELECT * FROM {1} WHERE 1=1" + (status.HasValue ? " AND [Status]=@Status;" : string.Empty);
                    var tpl = string.Format(a + b, DatabaseSchema.BaseSchema.Table.PaymentPlatform,
                                            DatabaseSchema.BaseSchema.Table.EBank);
                    var builder = new SqlBuilder();
                    if (status.HasValue)
                    {
                        builder.Where("[Status] = @Status", new { status });
                    }

                    return builder.AddTemplate(tpl);
                }
                else
                {
                    var builder = new SqlBuilder().Field("*")
                    .Table(DatabaseSchema.BaseSchema.Table.PaymentPlatform);
                    if (status.HasValue)
                    {
                        builder.Where("[Status] = @Status", new { status });
                    }
                    return builder.AddTemplate(BasicSingleTableSqlTemplate.Select);
                }

            }

            internal static SqlBuilder.Template GetPagedPaymentPlatform(int index = 1, int size = 20)
            {
                CheckIndexSize(ref index, ref size);
                var builder = new SqlBuilder().Field("*")
                .Table(DatabaseSchema.BaseSchema.Table.PaymentPlatform)
                .OrderBy("id desc")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });
                /*
                .Where("");
                */
                return builder.AddTemplate(BasicSingleTableSqlTemplate.PagedSelect);
            }
            #endregion PaymentPlatform
        }
    }
}